Basic Table |
| A basic table has three or more columns describing the same type of object. In a basic table, the primary key is composed of one column. Another column stores the name or human ID of the objects, and the remaining columns provide additional information of the object. For instance, the item table shown below has three columns. The primary key (item_id) is composed of only one column. The name is used by humans to identify the items. The remaining columns, in this case, the cost provides additional information about the item. |
| Problem 1 |
| Write the SQL commands to create the previous basic table including the data inside the table. Create a generic database called Info_sys. Call the file info_sys.sql. |
| info_sys.sql |
| USE master; GO ... CREATE TABLE dbo.item ( ... cost DECIMAL NOT NULL, ); INSERT INTO item (item_id, name, cost) VALUES (100, 'Monitor X8', 200.50); GO |
| Problem 2 |
| Provide an example of a basic table. Show a diagram and write the respective SQL code. The table must have at least four columns and four records. Use the generic database Info_sys. Call the file info_sys.sql. |
| Tip |
| The basic table can be used as a reference to estimate costs, and design duration of other more complex tables. Specifically, the estimated cost to design and create a basic table is denoted by BTC. Similarly, the time required to design a basic table is denoted by BTD. |
Lookup Table |
| A lookup table is the simplest table in a database. It maps information in the database so that it can be understood for humans. Usually, this table has only two columns. The first column is the primary key, and it has a value that is used by the database. The second column provides a human description of the tuple (record). For instance, the department table shown below has two columns. The letter C, E, M and A are internally used in the database to keep its reference integrity. The descr column is used to display information that can be understood by humans. The cost of a lookup table is 0.5*BTC. The time required to design a lookup table is 0.5*BTD. |
| Tip |
| Even though, the key of a lookup table can be of any data type, it is recommended to use positive integer values to simplify the programs of the information system. The lookup table maps directly to a drop down list or a set of radio buttons. |
| Problem 3 |
| Write the SQL commands to create the previous lookup table, including the data inside the table. Use the generic database called Info_sys. Call the file info_sys.sql. |
| info_sys.sql |
| USE master; GO ... CREATE TABLE dbo.department ( department_id CHAR NOT NULL PRIMARY KEY, ... ); INSERT INTO department (department_id, descr) VALUES ('C', 'Computation'); ... GO |
| Problem 4 |
| Provide an example of a lookup table. Show a diagram and write the respective SQL code. The table must have at least four records. Use the generic database Info_sys. Call the file info_sys.sql. |
Linking table |
| A linking table connects two or more tables using their primary keys. In a linking table, the primary key is composed of ALL THE COLUMNS in the table. In the diagram below, the account_client table is a linking table because it connects the account table and the client table. The primary key is composed of the account_id and the client_id because the account_id may appear several times in the table. In the same way, the client_id may appear several times in the table. Thus, to uniquely identify each row in the account_client table both columns must in the primary key. In other words, one combination of account_id and client_id can appear only once in the table. The cost of a linking table is 2*BTC. The time required to design a linking table is 2*BTD. |
| Problem 5 |
| Write the SQL commands to create the previous linking table. Use the generic database called Info_sys. Insert at least three records in each table. Call the file info_sys.sql. |
| info_sys.sql |
| USE master; GO ... CREATE TABLE dbo.client ( ... birth_date SMALLDATETIME NOT NULL ); GO INSERT INTO client (client_id, name, address_, birth_date) VALUES (2345, 'Sigal, Tobias', '56 Clayton Road', 'April 10, 1980'); GO CREATE TABLE dbo.account ( ... ); GO INSERT INTO account(account_id, balance) VALUES(120768, 234.56); ... GO CREATE TABLE dbo.account_client ( account_id INT REFERENCES account(account_id), client_id INT REFERENCES client(client_id), PRIMARY KEY (account_id, client_id) ); GO |
| Problem 6 |
| Provide an example of a linking table. Show a diagram and write the respective SQL code. Each table must have at least four records. Use the generic database Info_sys. Call the file info_sys.sql. |
Linking table with data |
| A linking table with data is a linking table that has one or more extra columns. These extra columns are not part of the primary key but provide additional information about the connection between the two tables. In the figure below, the item_client table provide the date when the client bought the items. The item_client table also stores the number of items that were bought. The cost of a linking table is 3*BTC. The time required to design a linking table is 3*BTD. |
| Problem 7 |
| Design a complete database with one linking table with data. (a) Draw a database diagram. (b) Write the SQL commands to create the table. Each table must have at least six records. |
Derived Table |
| A derived table is a table that is related to another table by the same primary key, that is, the meaning of the primary key is the same in both tables. The derived table provides additional information about the objects in the first table. The main difference is that the objects in the derived table are more specialized objects than the objects in the primary table. In the diagram shown below, the people table stores basic contact information of the people. The patient table stores additional information of the people in the people table. Thus, someone who is in the people table but not in the patient table is not a patient. In contrast, someone who is in both tables is a patient. Basically, the diagram express that a patient is a person first, then he is a patient. This type of table represents a one to one relation between the two tables and the concept of heritance of object oriented programming. The cost of a derived table is 4*BTC. The time required to design a derived table is 4*BTD. |
| Problem 8 |
| Design a database called university to manage class assignment and their schedules. The database must manage: classrooms, professors, courses and time periods (dates). Each classroom has a number of seats, and an optional projector. Each professor teaches a set of courses. The university has periods such as: semesters, trimesters, etc. Each period has a name, a start date, and an end date. Draw a complete database diagram. Create a SQL script to generate the database. Basic tables: professor, classroom, course, period, time_block. Linking tables: schedule. The time_block table has a seven bit variables (monday, thuesday, wednesday, ...), the time (i.e. 11:00 a.m.). Call the file university.sql. |
| Problem 9 |
| Design a database called best_buy to manage a store that sells electronics. The database must manage: products (items), item categories, item sales, item purchases, brands (item brands), clients, money, item history prices, and salesclerks. Draw a complete database diagram. Create a SQL script to generate the database. Basic tables: client, item, item_price_history(item_id pk, effective_date pk, item_price), brand, employee, payroll (employee_id, descr, salary, begin_date, end_date). Linking tables: buy_sell (buy_sell_id pk, employee_id, client_id, date), buy_sell_detail (buy_sell_id pk, item_id pk, item_count can be positive for inputs or negative for outputs, item_price), cart(client_id, item_id, item_count). Call the file circuit_city.sql. |
Storing values VS Computing a value |
| In the case of the age of a person, it is recommended to store his birthday and compute the age every time the command is executed. However, in some cases some transactions must be combined at some point in time to produce value that must be stored. For instance, if a store closes the sales every day, then at the end of the day all the transactions must be replaced by a single transaction that summarizes the day. You can delete the transactions of the day once they have been replaced by the summary transaction or you can move the transaction of the day to an archive table. Some companies close all their transactions at the end of the month, some companies every day and some companies every year. Your program must HAVE a close button so that the user can CLOSE all the transactions (no other user is connected to the database while the CLOSE procedure is executing.) After all the transactions have been closed the database is permanently modified and a report may optionally be generated. When running the CLOSE procedure report only errors. |